DataCleansing

This RMarkdown loads, clean and prepare data for further analysis

Loading First Data Set: “Dialy Air Quality Monitor”

From: https://www.hcup-us.ahrq.gov/reports/trendtables/summarytrendtables.jsp#export.

This Data Set contains the daily summary files with one record for each monitor that reported data for the given day

After Loading, we aggregate AQI US County Data by Month and per Year in order to prepare this data to merge with “HOspital Visits & Emergencies” Data Set from 2017 to 2020

A caption

Each NAAQS pollutant has a separate AQI scale, with an AQI rating of 100 corresponding to the concentration of the Federal Standard for that pollutant.

And this is the tibble with AQI Data aggregated by month:

head(aggtot)

## # A tibble: 6 x 8
##   State.Name county.Name yearmm       Cat Category           AQI Def_parm aggnum
##   <chr>      <chr>       <date>     <dbl> <chr>            <int> <chr>     <int>
## 1 Alabama    Baldwin     2017-01-01     1 Good                30 PM2.5         1
## 2 Alabama    Baldwin     2017-02-01     1 Good                38 PM2.5         1
## 3 Alabama    Baldwin     2017-03-01     2 Moderate            58 PM2.5         2
## 4 Alabama    Baldwin     2017-04-01     2 Moderate            74 Ozone         1
## 5 Alabama    Baldwin     2017-05-01     3 Unhealthy for S~   108 Ozone         1
## 6 Alabama    Baldwin     2017-06-01     2 Moderate            65 PM2.5         1

Merging with geographical data for US Counties, from r package (“sf”), and saving as RData

Next step is to merge AQI Data with US Counties geographical data with the objective to plot a US map with those values

Representing Air Quality on a US geographical detailed by county

–

Animated Map with Air Quality Indexes by US counties

In the map below we can see that the data collected is really useful for our purposes. The map represent Air Quality Indexes For the 2017 to 2020 years:

Loading Second Data Set “Hospital Visits & Emergencies”

From: https://www.hcup-us.ahrq.gov/reports/trendtables/summarytrendtables.jsp#export

This is the data for the Hospital Visits and Emergencies in US States from 2017 to 2020

This data is an excel report and the different States date are in different sheets of the same excel workbook

Map for Hospital Visits by US State per year and month

We have merged geographical US states data with the Hospital visits dataset to generate the animated map below

Loading the Third Data Set: US States population data from 2017 to 2020

From: https://www2.census.gov/programs-surveys/popest/datasets/2010-2020/state/totals/USA

With this data we can calculate the rates for Hospital Visits & Emergencies per Population in each US State

## # A tibble: 6 x 6
##   REGION DIVISION STATE NAME             POPULATION  YEAR
##   <chr>  <chr>    <int> <chr>                 <int> <dbl>
## 1 0      0            0 United States     325122128  2017
## 2 0      0            0 United States     326838199  2018
## 3 0      0            0 United States     328329953  2019
## 4 0      0            0 United States     329484123  2020
## 5 1      0            0 Northeast Region   56083383  2017
## 6 1      0            0 Northeast Region   56084543  2018